﻿using Hxj.Data;
using RecruitStudents.Utility.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using RecruitStudents.Utility;
using System.Data;
using RecruitStudents.Utility.Helper;
using RecruitStudents.Utility.Json;

namespace RecruitStudents.Client.BLL.Impl
{
    public class BLLComm
    {
        DAL.DALComm dal = new DAL.DALComm();
        public DataTable GetDicDataTableByDicTableName(string strTableName)
        {
            string sqlString = string.Format("select * from {0}  where IsActive=1", strTableName);
            return dal.GetTable(sqlString);
        }

        public JsonMessage SaveSystemInfo(string strJson)
        {
            DbTrans trans = DbHelper.GetDatabase().BeginTransaction();
            JsonMessage message = new JsonMessage();
            try
            {
                List<TB_Sys_SystemInfo> list = strJson.ToList<List<TB_Sys_SystemInfo>>();
                foreach (TB_Sys_SystemInfo model in list)
                {
                    //判断当前年份是否存在
                    TB_Sys_SystemInfo mdl = dal.GetModel<TB_Sys_SystemInfo>(TB_Sys_SystemInfo._.SystemInfoID == model.SystemInfoID);
                    if (mdl != null)
                    {
                        mdl.Attach();
                        mdl.SystemInfoID = model.SystemInfoID;
                        mdl.SystemInfoName = model.SystemInfoName;
                        mdl.SystemInfoRemark = model.SystemInfoRemark;
                        dal.Save<TB_Sys_SystemInfo>(mdl, trans);
                    }
                    else
                    {
                        dal.Save<TB_Sys_SystemInfo>(model, trans);
                    }
                }
                trans.Commit();
                message.State = "true";
                message.Msg = "保存成功!";
            }
            catch (Exception ex)
            {
                trans.Rollback();
                message.State = "false";
                message.Msg = "保存失败," + ex.Message;
                throw ex;
            }
            finally
            {
                trans.Close();
            }
            return message;
        }
        #region 用户登陆
        public TB_Sys_User GetUserLogin(string strLoginName, string strPassword)
        {
            WhereClip wcp = new WhereClip();
            wcp = wcp.And(TB_Sys_User._.LoginName == strLoginName);
            wcp = wcp.And(TB_Sys_User._.Password == strPassword.ToSafeEncrypt());
            TB_Sys_User model = dal.GetModel<TB_Sys_User>(wcp);
            return model;
        }

        public DataTable GetRoleAuthModuleTable(string strRoleID)
        {
            return dal.GetRoleAuthModuleTable(strRoleID);
        }

        public DataTable GetSystemInfoDataTable(string strSystemInfoID)
        {
            WhereClip wcp = new WhereClip();
            wcp = wcp.And(TB_Sys_SystemInfo._.SystemInfoID.SelectIn(strSystemInfoID.Split(',')));
            return dal.GetTable<TB_Sys_SystemInfo>(wcp);
        }

        public TB_Sys_SystemInfo GetSystemInfoModel(string strSystemInfoID)
        {
            WhereClip wcp = new WhereClip();
            wcp = wcp.And(TB_Sys_SystemInfo._.SystemInfoID == strSystemInfoID);
            return dal.GetModel<TB_Sys_SystemInfo>(wcp);
        }
        #endregion

        #region CS端登陆的时候选择考点/专业/级别/考场
        /// <summary>
        /// 根据考务表获取已分配的考点
        /// </summary>
        /// <returns></returns>
        public DataTable GetExamPlaceTableALL_CS()
        {
            string strSql = @"select * from TB_Sys_ExamPlace where ExamPlaceNO in (
                select Distinct ExamPlaceNO from TB_Exam_Entretenimientoh
                )";
            return dal.GetTable(strSql);
        }
        /// <summary>
        /// 根据考点获取专业
        /// </summary>
        /// <param name="strExamPlaceID"></param>
        /// <returns></returns>
        public DataTable GetSpecialityKindsTableByExamPlaceID_CS(string strExamPlaceID)
        {
            WhereClip wcp = new WhereClip();
            wcp = wcp.And(TB_Sys_SpecialityKinds._.IsActive == "1");
            wcp = wcp.And(TB_Sys_SpecialityKinds._.SpecialityKindsNO.SubQueryIn(DbHelper.GetDatabase().From<TB_Sys_ExamPlaceSpecialityKindsMapping>().Select(TB_Sys_ExamPlaceSpecialityKindsMapping._.SpecialityKindsNO)
                .Where(TB_Sys_ExamPlaceSpecialityKindsMapping._.ExamPlaceNO == strExamPlaceID)));
            return dal.GetTable<TB_Sys_SpecialityKinds>(wcp);
        }
        /// <summary>
        /// 根据考点专业获取考试级别
        /// </summary>
        /// <returns></returns>
        public DataTable GetExamLevelByPlaceID_Speciality_CS(string strExamPlaceID, string strSpecialityKindsNO)
        {
            string strSql = @"select * from TB_Dic_ExamLevel where DicNO in (
                select Distinct ExamLevel from TB_Exam_Entretenimientoh where ExamPlaceNO='{0}' and SpecialityKindsNO like '{1}%'
            )";
            return dal.GetTable(string.Format(strSql, strExamPlaceID, strSpecialityKindsNO));
        }
        /// <summary>
        /// 根据考点专业级别获取考场
        /// </summary>
        /// <returns></returns>
        public DataTable GetExamRoomByPlaceID_Speciality_Level_CS(string strExamPlaceID, string strSpecialityKindsNO, string strExamLevel)
        {
            if (strSpecialityKindsNO.Split(',').Length > 1)
            {
                string strSql = @"select * from TB_Exam_ExamRoom where EntretenimientohID in (
            select EntretenimientohID from TB_Exam_Entretenimientoh where ExamPlaceNO='{0}' and ExamLevel='{2}' and SpecialityKindsNO in ({1})
            )";
                return dal.GetTable(string.Format(strSql, strExamPlaceID, strSpecialityKindsNO, strExamLevel));
            }
            else
            {
                //    string strSql = @"select * from TB_Exam_ExamRoom where EntretenimientohID in (
                //select EntretenimientohID from TB_Exam_Entretenimientoh where ExamPlaceNO='{0}' and SpecialityKindsNO='{1}' and ExamLevel='{2}'
                //)";
                string strSql = @"select * from TB_Exam_ExamRoom where EntretenimientohID in (
            select EntretenimientohID from TB_Exam_Entretenimientoh where ExamPlaceNO='{0}' and SpecialityKindsNO like '{1}%' and ExamLevel='{2}'
            )";
                return dal.GetTable(string.Format(strSql, strExamPlaceID, strSpecialityKindsNO, strExamLevel));
            }
        }

        public TB_Exam_Entretenimientoh GetEntretenimientohModel(string examLevel, string examPlaceID, string specialityKindsNO)
        {
            WhereClip wcp = new WhereClip();
            wcp = wcp.And(TB_Exam_Entretenimientoh._.ExamLevel == examLevel && TB_Exam_Entretenimientoh._.ExamPlaceNO == examPlaceID
                && TB_Exam_Entretenimientoh._.SpecialityKindsNO == specialityKindsNO);
            return dal.GetModel<TB_Exam_Entretenimientoh>(wcp);
        }
        #endregion

        /// <summary>
        /// 生成业务字典表的编码
        /// </summary>
        /// <param name="strTableName"></param>
        /// <param name="strExamYear"></param>
        /// <returns></returns>
        public string CreateTableAppNo(string strTableName, string strExamYear)
        {
            try
            {
                return dal.CreateTableAppNo(strTableName, strExamYear);
            }
            catch
            {
                return Guid.NewGuid().ToSafeString().Replace("-", "");
            }
        }

        /// <summary>
        /// 根据考场号获取老师列表信息
        /// </summary>
        /// <param name="strExamRoomID"></param>
        /// <returns></returns>
        public DataTable GetTeacherList(string strExamRoomID)
        {
            string strSql = @"select 
                                            Row_number() Over(order by b.LoginName) as XH,
                                            b.LoginName,b.ExaminerName,b.SpecialityKinds_DetailNO   
                                          from 
                                              TB_Exam_ExamRoomExaminerMapping as a 
                                          inner join 
                                              TB_Exam_Examiner as b 
                                           on a.ExaminerID=b.ExaminerID 
                                        where a.IsActive='1' and a.ExamRoomNO='{0}' order by b.LoginName                                        
                                         ";
            strSql = string.Format(strSql, strExamRoomID.RemoveSqlInjection());
            return dal.GetTable(strSql);
        }

        /// <summary>
        /// 通过考务ID获取考场列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetExamRoomTableByEntretenimientohID(string EntretenimientohID)
        {
            WhereClip clip = new WhereClip();
            clip = clip.And(TB_Exam_ExamRoom._.EntretenimientohID == EntretenimientohID);
            clip = clip.And(TB_Exam_ExamRoom._.IsActive == "1");
            return dal.GetTable<TB_Exam_ExamRoom>(clip);

        }

        /// <summary>
        /// 获取专业列表
        /// </summary>
        /// <returns></returns>
        public DataTable GetSpecialityKindsTable(string strExamYear)
        {
            WhereClip clip = new WhereClip();
            clip = clip.And(TB_Sys_SpecialityKinds._.IsActive == "1");
            clip = clip.And(TB_Sys_SpecialityKinds._.ExamYear == strExamYear);
            return dal.GetTable<TB_Sys_SpecialityKinds>(clip);
        }
    }
}
